ORACLE---Unit04： SQL（高级查询）

---（重点复习：子查询、分页查询、decode）
--- SQL（高级查询）

--- 子查询
--- 子查询是嵌套在其它SQL语句当中的，目的是为嵌套的SQL提供数据，以便其执行。

---查看谁的工资高于CLARK?
---1.先查CLARK的工资
SELECT sal FROM emp_RR WHERE ename='CLARK';
--->2450
---2.工资高于CLARK的员工信息
SELECT ename,sal FROM emp_RR WHERE sal>2450;
--->5条

SELECT ename,sal FROM emp_RR WHERE sal>
(SELECT sal FROM emp_RR WHERE ename='CLARK');
---子查询加括号，放在括号里面。为了外层数据，而提供数据的。

---查询谁跟FORD一个部门的
SELECT ename,deptno FROM emp_RR WHERE deptno=
(SELECT deptno FROM emp_RR dept_RR WHERE ename='FORD'); ---查看20号部门的员工
--->4条记录
SELECT * FROM dept_RR;

---查看工资高于公司平均工资的员工？
SELECT ename,sal FROM emp_RR WHERE sal>
(SELECT AVG(sal) FROM emp_RR );

---查看公司最高工资是谁？
SELECT ename,sal FROM emp_RR WHERE sal=
(SELECT MAX(sal) FROM emp_RR); ---最高工资5000

---查看部门的平均工资，前提是该部门的平均工资要高于30号部门的平均工资？
SELECT AVG(sal) FROM emp_RR WHERE deptno=30;
--->1566.666666666666666666666666666666666667

SELECT AVG(sal),deptno FROM emp_RR
GROUP BY deptno HAVING AVG(sal)>
(SELECT AVG(sal) FROM emp_RR WHERE deptno=30);

---在DDL与DML中也可以使用子查询
---1：在DDL中使用子查询，可以将一个子查询的结果集当做表快速创建。

---创建一张表，含有empno,ename,sal,job,deptno,dname,loc.
---并且数据就是现有表emp,dept中的数据？

CREATE TABLE emp_RRYY AS
SELECT e.empno,e.ename,e.sal,e.job,d.deptno,d.dname,d.loc FROM emp_RR e,dept_RR d
WHERE e.deptno=d.deptno(+);
--->少了scott
--->table EMP_RRYY 已创建。
DESC emp_RRYY
SELECT * FROM emp_RRYY;
DROP TABLE emp_RRYY;
--->table EMP_RRYY已删除。

CREATE TABLE emp_RRYY AS
SELECT e.empno id,e.ename name ,e.sal,e.job,d.deptno,d.dname,d.loc FROM emp_RR e,dept_RR d
WHERE e.deptno=d.deptno(+);
---在子查询中，如果字段取了别名，新创建的表对应的字段就是用的别名。没有别名，就是用字段名。

---DML中使用子查询
---将SIMTH所在部门所有员工工资上浮10%
SELECT deptno FROM emp_RR WHERE ename='SMITH'; ---SMITH部门号是20

UPDATE emp_RR SET sal=sal*1.1 WHERE deptno=
(SELECT deptno FROM emp_RR WHERE ename='SMITH');
--->4 行已更新。
ROLLBACK;
--->回退完成。
SELECT * FROM emp_RR;

---将SIMITH所在部门的员工都删了
DELETE FROM emp_RR WHERE deptno=
(SELECT deptno FROM emp_RR WHERE ename='SMITH');
--->4 行已删除。(只有10条记录了)
ROLLBACK;
--->回退完成。

--- 一、子查询 (像Java中的内部类，嵌套在其它SQL语句当中的)
--- 1.子查询在WHERE子句中
--- 1.1）在SELECT查询中，在WHERE查询条件中的限制条件不是一个确定的值，而是来自于另外一个查询的结果
--- 1.2）为了给查询提供数据而首先执行的查询语句叫做子查询
--- 1.3）子查询是嵌入在其它SQL语句中的SELECT语句，大部分时候出现在WHERE子句中
--- 1.4）子查询嵌入的语句称作主查询或父查询
--- 1.5）主查询可以是SELECT语句，也可以是其它类型的语句比如DML或DDL语句

--- 1.6）根据饭后结果的不同，子查询可分为单行子查询、多行子查询及多列子查询
--- 1）单行单列子查询：一行一列就是一个值。
--- 单列大多数当做过滤条件，放在where、having里去使用。
--- 2）多行单列子查询：多行一列就是好几个值。
--- 3）多行多列子查询：多行多列就是两个维度。
--- 多列大多数当做一张表使用，表进行查询工作。
--- 1.7）
---查找和SCOTT同职位的员工：
SELECT e.ename,e.job FROM emp_RR e
WHERE e.job=
(SELECT job FROM emp_RR WHERE ename='SCOTT'); ---先执行子查询得到结果后再进行主查询
---查找薪水比整个机构平均薪水高的员工
SELECT deptno,ename,sal
FROM emp_RR e
WHERE sal>(SELECT AVG(sal)FROM emp_RR);

---子查询根据查询结果不同，分为：单行单列子查询、多行单列子查询和多行多列子查询。
---其中单列子查询常用在过滤条件中，而多列子查询通常当做表看待。

---对于多行单列子查询在作为过滤条件进行判断时，要配合IN、ANY、ALL使用

---查看与SALESMAN同部门的其他职位员工？
SELECT deptno,ename FROM emp_RR WHERE job='SALESMAN';

SELECT ename,job,deptno FROM emp_RR WHERE deptno=
(SELECT deptno FROM emp_RR WHERE job='SALESMAN');
--->ORA-01427: 单行子查询返回多个行

SELECT ename,job,deptno FROM emp_RR WHERE deptno IN
(SELECT deptno FROM emp_RR WHERE job='SALESMAN');

SELECT ename,job,deptno FROM emp_RR WHERE deptno IN
(SELECT deptno FROM emp_RR WHERE job='SALESMAN')
AND job<>'SALESMAN';

---查看比职位是SALESMAN和CLERK工资都高的员工？
SELECT ename,sal FROM emp_RR
WHERE sal>ALL(SELECT sal FROM emp_RR WHERE job IN('SALESMAN','CLERK'));

--- 1.8）如果子查询返回多行，主查询中要使用多行比较操作符
--- 1.9）多行比较操作符包括IN、ALL、ANY。其中ALL和ANY不能单独使用，
--- 需要配合单行比较操作符>、>=、<、<=一起使用
---查询出部门中有SALESMAN但职位不是SALESMAN的员工的信息：
SELECT empno,ename,job,sal,deptno
FROM emp_RR
WHERE deptno IN
(SELECT deptno FROM emp_RR WHERE job='SALESMAN') ---子查询的结果是多个值
AND job<>'SALESMAN';

--- 2.0）在子查询中需要引用到主查询的字段数据，使用EXISTS关键字
--- 2.1）EXISTS后边的子查询至少返回一行数据，则整个条件返回TRUE.
--- 2.2）列出来那些有员工的部门信息
SELECT deptno,dname FROM dept_RR d
WHERE EXISTS
(SELECT * FROM emp_RR e WHERE d.deptno=e.deptno);

---EXISTS关键字
---EXISTS关键字用在过滤条件中，其后需要跟一个子查询，当该子查询可以查询出至少一条记录时，即判断为满足条件。

---查看有员工的部门？
SELECT d.deptno,d.dname,d.loc FROM dept_RR d ---查出dept表中的所有信息
WHERE EXISTS
(SELECT * FROM emp_RR e WHERE e.deptno=d.deptno);
--->40号部门没有

SELECT d.deptno,d.dname,d.loc FROM dept_RR d
WHERE NOT EXISTS
(SELECT * FROM emp_RR e WHERE e.deptno=d.deptno);
--->40 OPERATIONS BOSTON

---查看有下属的员工？
SELECT m.empno,m.ename,m.sal FROM emp_RR m
WHERE EXISTS
(SELECT * FROM emp_RR e WHERE e.mgr=m.empno);

--- 2.子查询在HAVING子句中
---查询列出最低薪水高于部门30的最低薪水的部门信息
SELECT deptno,MIN(sal) min_sal FROM emp_RR
GROUP BY deptno
HAVING MIN(sal)>
(SELECT MIN(sal)FROM emp_RR WHERE deptno=30);

--- 3.子查询在FROM部分
--- 3.1)FROM子句用来指定要查询的表
--- 3.2)如果要在一个子查询的结果中继续查询，则子查询出现在FROM 子句中，这个子查询也称作行内视图或匿名视图
--- 3.3）把子查询当做视图对待，但视图没有名字，只能在当前的SQL语句中有效

---查询出薪水比本部门平均薪水高的员工信息
SELECT e.deptno,e.ename,e.sal FROM emp_RR e,
(SELECT deptno,AVG(sal) avg_sal FROM emp_RR GROUP BY deptno)x
WHERE e.deptno=x.deptno and e.sal>x.avg_sal
ORDER BY e.deptno;

---FROM子句中使用子查询
---当一个子查询为多列子查询时，通常是当做一张表使用出现在FROM子句中

---查看谁的工资高于自己所在部门平均工资？
---1.每个部门的平均工资是多少
SELECT AVG(sal),deptno FROM emp_RR GROUP BY deptno;
--->有4个部门的平均工资数

SELECT deptno,AVG(sal)FROM emp_RR GROUP BY deptno;

---SELECT e.ename,e.sal,e.deptno FROM emp_RR e,T t
---WHERE e.deptno=t.deptno
---AND e.sal>t.AVG(sal);

SELECT e.ename,e.sal,e.deptno FROM emp_RR e,(SELECT deptno,AVG(sal)FROM emp_RR GROUP BY deptno) t
WHERE e.deptno=t.deptno
AND e.sal>t.AVG(sal);
--->ORA-00904: "T"."AVG": 标识符无效
--->聚合函数：不能出现变量值。

SELECT e.ename,e.sal,e.deptno FROM emp_RR e,(SELECT deptno,AVG(sal) avg_sal FROM emp_RR GROUP BY deptno) t
WHERE e.deptno=t.deptno
AND e.sal>t.avg_sal;

---查看每个部门最高工资是谁？
SELECT e.ename,e.sal,e.deptno FROM emp_RR e,(SELECT deptno,MAX(sal) max_sal FROM emp_RR GROUP BY deptno) t
WHERE e.deptno=t.deptno
AND e.sal=t.max_sal;

--- 4.子查询在SELECT部分(把查询的结果当成字段来用)
--- 把子查询放在SELECT 子句部分，可以认为是外连接的另一种表现形式，使用更灵活
SELECT e.ename,e.sal,
(SELECT d.deptno FROM dept_RR d WHERE d.deptno=e.deptno) deptno
FROM emp_RR e;

SELECT e.ename,e.sal,
(SELECT d.dname FROM dept_RR d WHERE d.deptno=e.deptno) dname
FROM emp_RR e;
---外连接的写法
---把查询的结果当成字段值来用(这种查询将来用的少，大部分子查询都是用在WHERE,HAVING里)

---分页查询
---当一个查询语句查询的数据量非常大时，需要分批分段将数据查询出来，这样做可以减少系统资源开销，提高数据库响应速度。
---分页由于在标准SQL中没有定义，所以不同的数据库有不同的语法，即：方言。

---ROWNUM：伪列
---ORACLE提供了伪列：ROWNUM来配合完成分页查询。
---ROWNUM不存在与任何表中，但是所有的表都可以查询该字段。该字段的值在结果集中就是每条记录的行号。
---ROWNUM的值是动态生成的，是伴随查询的过程中生成的。即：每当可以从表中查询出一条记录时，
---ROWNUM会为该条记录生成行号，从1开始逐一递增。
SELECT ROWNUM ename,sal,job,deptno FROM emp_RR;

---查询6到10？？？正常是一条记录都没有。
SELECT ROWNUM ename,sal,job,deptno FROM emp_RR WHERE ROWNUM BETWEEN 6 AND 10;
--->假的SQL?O(∩_∩)O哈哈~why？下午再说PM SEE YOU LA LA

---在使用ROWNUM对结果集编号的过程中不要使用ROWNUM做>1以上数字的判断，否则查询不出任何结果。
SELECT ROWNUM ename,sal,job,deptno FROM emp_RR WHERE ROWNUM>1;
SELECT ROWNUM ename,sal,job,deptno FROM emp_RR WHERE ROWNUM<10;

SELECT * FROM(SELECT ROWNUM,ename,sal,job,deptno FROM emp_RR)WHERE ROWNUM BETWEEN 6 AND 10;
--->然并卵！外层的rownum不是里面的字段
SELECT * FROM(SELECT ROWNUM rn,ename,sal,job,deptno FROM emp_RR)WHERE rn BETWEEN 6 AND 10;

---工资排名6到10的人。
SELECT * FROM(SELECT ROWNUM rn,ename,sal,job,deptno FROM emp_RR)WHERE rn BETWEEN 6 AND 10
ORDER BY sal DESC;
--->ORDER BY是最后干。

SELECT *
FROM(SELECT ROWNUM rn,t. *
FROM(SELECT ename,sal,job deptno FROM emp_RR ORDER BY sal DESC)t)
WHERE rn BETWEEN 6 AND 10;
---先排序，后编号，再取范围。套两个子查询。虽然查出来，但是效率较低。
---排序一定要干的，编号上可以让它效率更高一些，

SELECT *
FROM(SELECT ROWNUM rn,t. *
FROM(SELECT ename,sal,job deptno FROM emp_RR ORDER BY sal DESC)t WHERE ROWNUM <=10)
WHERE rn >=6;
---在里层编号的内容，多余的就可以省去了。提高效率。

--- 二、分页查询(分段查询---淘宝、京东搜一个U盘有成千上万个，不会在同一页...56K猫，一拨号上网，电话就不能打了。多图杀猫。擒拿少取)
---ORACLE的分页和MySQL的分页写法不一样
--- 1.ROWNUM
--- 1.1) 被称作伪列，用于返回标识行数据顺序的数字
SELECT ROWNUM,empno,ename,sal FROM emp_RR;
--- 1.2) 只能从1计数，不能从结果集中直接截取
SELECT ROWNUM,empno,ename,sal FROM emp_RR WHERE rownum>3; ---查询不到结果
--- 1.3）利用ROWNUM截取结果集中的部分数据，需要用到行内视图
SELECT * FROM
(SELECT ROWNUM rn,e.*FROM emp e) ---给ROWNUM一个别名
WHERE rn BETWEEN 8 AND 10;

--- 2.使用子查询进行分页
--- 2.1)分页策略：每次只取一页的数据。每次换页，取下一页的数据。
--- 2.2）在oracle中利用ROWNUM的功能可用来进行分页
--- 2.3）假设结果集共105条，每20条分为一页，则：
--- Page1:1至20
--- Page2:21至40
--- ...
--- PageN:(n-1)*pageSize+1至n*pageSize
--- 共6页

--- 3.分页与ORDER BY
--- 3.1)按薪水倒序排列，取出结果集中第8到第10条的记录
SELECT * FROM (SELECT ROWNUM rn,t.*FROM
(SELECT empno,ename,sal FROM emp_RR ORDER BY sal DESC)t)
WHERE rn BETWEEN 8 AND 10

--- 3.2）根据要查看的页数，计算起点值((n-1)*pageSize+1)和终点值(n*pageSize),
--- 替换掉BETWEEN和AND的参数，即得到当前页的记录

---在将来实际开发中，不可能让用户去写这两个数的。例如：上淘宝只需要写上一页、下一页或则跳到第几页，最多能显示显示1能显示多少条。
---从来不会让你写第几条到第几条。

---pageSize：每页显示的条目数
---page：页数

---pageSize：5 page：2 --->也就是6到10条

---算法：现成的推导
---start：(page-1)*pageSize+1
---end:pageSize*page

---在Java中怎么干？将来是动态的，让Java去执行SQL语句。动态的把SQL语句拼出来。
---伪代码，理解就行。（下周就写）

---******************************************************************************
package oracle;

import java.util.Scanner;

public class Test {

public static void main(String[] args) {
Scanner scanner= new Scanner(System.in);
System.out.println("请输入每页要显示的条目数：");
int pageSize=Integer.parseInt(scanner.nextLine());

System.out.println("请输入要查看的页数");
int page=Integer.parseInt(scanner.nextLine());

int start=(page-1)*pageSize+1;
int end=pageSize * page;

String sql="SELECT *"+
"FROM(SELECT ROWNUM rn,t. *"+
" FROM(SELECT ename,sal,job deptno FROM emp_RR ORDER BY sal DESC)t WHERE ROWNUM <="+end+")"+
" WHERE rn >="+start;
System.out.println(sql);
}

}
//输出后得到如下内容：（将来把这条语句发送到数据库，相当于在数据库里执行。）
//SELECT *
//FROM(SELECT ROWNUM rn,t. *
//FROM(SELECT ename,sal,job deptno FROM emp_RR ORDER BY sal DESC)t WHERE ROWNUM <=10)
//WHERE rn >=6;
---*******************************************************************************************
---到数据库显示：
SELECT *
FROM(SELECT ROWNUM rn,t. *
FROM(SELECT ename,sal,job deptno FROM emp_RR ORDER BY sal DESC)t WHERE ROWNUM <=10)
WHERE rn >=6;

***********************************************************************************************
---当有了框架后，就不是写在Java只中，而是写在配置文件中，配置文件就可以编写SQL语句，让Java去执行。

 

--- 三、DECODE函数
--- 1.DECODE函数基本语法
--- 1.1)DECODE(expr,search1,result1[,serch2,result2...][,default]) ---至少传3个以上参数

--- 1.2)DECODE用于比较参数expr的值，如果匹配到哪一个search条件，就返回对应的result结果
--- 1.3）可以有多组search和result的对应关系，如果任何一个search条件都没有匹配到，则返回最后default的值
--- 1.4）default参数是可选的，如果所有提供default参数值，当没有匹配到时，将返回NULL.

---查询职员表，根据职员的职位计算奖励金额，当职位分别是'MANAGER'、'ANALYST'、'SALESMAN'时，
---奖励金额分别是薪水的1.2倍、1.1倍、1.05倍，如果不是这三个职位，则奖励金额取薪水值
SELECT ename,job,sal,
DECODE(job,'MANAGER',sal*1.2,'ANALYST',sal*1.1,'SALESMAN',sal*1.05,sal)bonus FROM emp_RR;

SELECT ename,job,sal,
DECODE(job,
'MANAGER',sal*1.2,
'ANALYST',sal*1.1,
'SALESMAN',sal*1.05)bonus
FROM emp_RR;
--->不返回正常工资，而返回空值null。

---和DECODE 函数功能相似的有CASE语句，实现类似于if-else的操作。（更像Java中的switch...case...）
SELECT ename,job,sal,
CASE job WHEN 'MANAGER' THEN sal*1.2
WHEN 'ANALYST' THEN sal*1.1
WHEN 'SALESMAN' THEN sal*1.05
ELSE sal END
bonus FROM emp_RR;

--- 2.DECODE函数中分组查询中的应用
--- 2.1按字段内容分组
--- 场景：计算职位的人数，analyst/manager属于vip，其余是普通员工operation,无法用GROUP BY简单实现
SELECT DECODE(job,'ANALYST','VIP','MANAGER','VIP','OPERATION')job,
COUNT(1)job_cnt FROM emp_RR
GROUP BY DECODE(job,'ANALYST','VIP','MANAGER','VIP','OPERATION');

--- 2.2按字段内容排序
--- 场景：Dept表中按"OPERATIONS"、"ACCOUNTING"、"SALES"排序，无法按照字面数据排序
SELECT deptno,dname,loc FROM dept_RR
ORDER BY
DECODE(dname,'OPERATIONS',1,'ACCOUNTING',2,'SALES',3);
---自由定义排序规则.数据量少的话，可以排。
---DECODE函数不需要所有的参数，都是同一类型的，比较的是相同类型的，返回的也是相同类型的，即可。

---在分组中使用DECODE 函数可以将字段值不同的记录划分为一组(将来也实用)
---将MANAGER和ANALYST看作一组，其他职位看作另一组，分别统计各组人数

SELECT ename,job FROM emp_RR ORDER BY job;
SELECT COUNT(1),job FROM emp_RR GROUP BY job;
---统计人数的时候，count什么都行。出来的是有几组职位，每个职位出来的计数。

SELECT ename,job,
DECODE(job,
'MANAGER','VIP',
'ANALYST','VIP',
'OTHER')
FROM emp_RR;

SELECT COUNT(1),
DECODE(job,
'MANAGER','VIP',
'ANALYST','VIP',
'OTHER') count_name
FROM emp_RR
GROUP BY DECODE(job,
'MANAGER','VIP',
'ANALYST','VIP',
'OTHER');

 

--- 四、排序函数(没那么常用)
--- ROW_NUMBER、RANK、DENSE_RANK
---1.ROW_NUMBER
--- 1.1）ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col2)
--- 1.2）表示根据col1分组，在分组内部根据col2排序
--- 1.3）此函数计算的值就表示每组内部排序后的顺序编号，组内连续其唯一
--- 1.4）Rownum是伪列，ROW_NUMBER功能更强，可以直接从结果集中取出子集

--- 场景：按照部门编码分组显示，每组内按职员编码排序，并赋予组内编码
SELECT deptno,ename,empno,
ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY empno)AS emp_id FROM emp_RR;

---排序函数：将来在开发中解决特殊问题，它能帮你把整个的结果集按照指定的字段分组，然后在组内帮你排序。然后生成一个组内行号（编号）。
---rownum是对整个结果集123456的排序，而它不是。它是每组123456，再一组123456.

---排序函数
---排序函数可以对结果集按照指定的字段分组，然后在组内按照指定的字段排序，然后生成组内的行号。
---ROW_NUMBER:生成组内连续且唯一的数字
---查看每个部门的工资排名：
SELECT ename,sal,deptno,
ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY sal DESC) rank
FROM emp_RR;
--->就像Justin biber的歌 背呗背呗... ...
---有两人不爽：
---WARD 1250 30 4
---MARTIN 1250 30 5

---RANK：生成组内不连续也不唯一的数字
SELECT ename,sal,deptno,
RANK()OVER(PARTITION BY deptno ORDER BY sal DESC) rank
FROM emp_RR;
---WARD 1250 30 4
---MARTIN 1250 30 4
---JAMES 950 30 6--->变5

---DENSE_RANK:生成组内连续但不唯一的数字
SELECT ename,sal,deptno,
DENSE_RANK()OVER(PARTITION BY deptno ORDER BY sal DESC) rank
FROM emp_RR;
---WARD 1250 30 4
---MARTIN 1250 30 4
---JAMES 950 30 5

 

---2.RANK
--- 2.1)PANK()OVER(PARTITION BY col1 ORDER BY col2)
--- 2.2)表示根据col1分组，在分组内部根据col2给予等级标识
--- 2.3）等级标识即排名，相同的数据返回相同排名
--- 2.4）跳跃排序，如果有相同数据，则排名相同，比如并列第二，则两行数据都标记为2，但下一位将是第四名
--- 2.5）和ROW_NUMBER的区别是有重复值，而ROW_NUMBER没有

--- 场景：按照部门编码分组，同组内按薪水倒序排序，相同薪水则按奖金数正序排序，并给予组内等级，用Rank_ID表示
SELECT deptno,ename,sal,comm,
RANK()OVER(PARTITION BY deptno ORDER BY sal DESC,comm) "Rank_ID" FROM emp_RR;

---3.DENSE_RANK
--- 3.1)DENSE_RANK()OVER(PARTITION BY col1 ORDER BY col2)
--- 3.2)表示根据col1分组，在分组内部根据col2给予等级标识
--- 3.3）即排名，相同的数据返回相同排名
--- 3.4）连续排序，如果有并列第二，下一个排序将是三，这一点是和RANK的不同,RANK是跳跃排序

---场景：关联emp和dept表，按照部门编码分组，每组内按照员工薪水排序，列出员工的部门名字、姓名和薪水
SELECT d.dname,e.ename,e.sal,
DENSE_RANK()OVER(PARTITION BY e.deptno ORDER BY e.sal)
AS drank
FROM emp_RR e join dept_RR d
on e.deptno=d.deptno;

----随机插入一千条数据在里面：（随机数,随机生成。）
---ROLLUP(续3)
CREATE TABLE sales_tab_RR(
year_id NUMBER NOT NULL,
month_id NUMBER NOT NULL,
day_id NUMBER NOT NULL,
sales_value NUMBER(10,2)NOT NULL
);
INSERT INTO sales_tab_RR
SELECT TRUNC(DBMS_RANDOM.value(2010,2012))AS year_id,
TRUNC(DBMS_RANDOM.value(1,13))AS month_id,
TRUNC(DBMS_RANDOM.value(1,32))AS day_id,
ROUND(DBMS_RANDOM.value(1,100),2) AS sales_value
FROM dual
CONNECT BY level<=1000;
--->table SALES_TAB_RR 已创建。
--->1,000 行已插入。

 

 

 

--- 五、集合操作（并、交、差）
--- 1.UNION、UNION ALL
--- 1.1）为了合并多个SELECT语句的结果，可以使用集合操作符，实现集合的并、交、差
--- 1.2）集合操作符包括UNION、UNION ALL、INTERSECT和MINUS
--- 1.3）多条作集合操作的SELECT语句的列的个数和数据类型必须匹配
--- 1.4）ORDER BY子句只能放在最后的一个查询语句中
--- 1.5）集合操作的语法如下：
---SELECT statement1
---[UNION|UNION ALL|INTERSECT|MINUS]
---SELECT statement2;

--- 1.6）用来获取两个或两个以上结果集的并集
--- 1.7）UNION操作符会自动去掉合并后的重复记录
--- 1.8）UNION ALL返回两个结果集中的所有行，包括重复的行。
--- 1.9）UNION操作符对查询结果排序，UNION ALL不排序

--- 2.0）合并职位是'MANAGER'的员工和薪水大于2500的员工集合，查看两种方式的结果差别
SELECT ename,job,sal FROM emp_RR
WHERE job='MANAGER'
UNION
SELECT ename,job,sal FROM emp_RR
WHERE sal>2500;
--->6条记录

SELECT ename,job,sal FROM emp_RR
WHERE job='MANAGER'
UNION ALL
SELECT ename,job,sal FROM emp_RR
WHERE sal>2500;
--->8条记录

--- 2.INTERSECT
--- 2.1)获得两个结果集的交集，只有同时存在于两个结果集中的数据，才被显示输出
--- 2.2）使用INTERSECT操作符后的结果集会以第一列的数据作升序排列
--- 2.3）显示职位是'MANAGER'的员工薪水大于2500的员工的交集
SELECT ename,job,sal FROM emp_RR
WHERE job='MANAGER'
INTERSECT
SELECT ename,job, sal FROM emp_RR
WHERE sal>2500;

--- 3.MINUS
--- 3.1)获取两个结果集的差集
--- 3.2）只有在第一个结果集中存在，在第二个结果集中不存在的数据，才能够被显示出来。也就是结果集一减去结果集的结果
--- 3.3）列出职位是MANAGER但薪水低于2500的员工记录
SELECT ename,job,sal FROM emp_RR
WHERE job='MANAGER'
MINUS
SELECT ename,job,sal FROM emp_RR
WHERE sal>=2500;

SELECT * FROM emp_RR;

SELECT * FROM dept_RR;

 

--- 六、高级分组函数
--- ROLLUP、CUBE、GROUPING SETS
--- 1.ROLLUP
--- 1.1)ROULLUP、CUBE和GROUPING SETS运算符是GROUP BY子句的扩展，可以生成与使用UNION ALL来组合单个分组查询时
--- 相同的结果集，用来简化和高效的实现统计查询
--- GROUP BY ROLLUP(a,b,c)
--- GROUP BY CUBE(a,b,c)
--- GROUP BY GROUPING SETS((a),(b))

--- 1.2)假设有表test,有a、b、c、d四个列。
---SELECT a,b,c,SUM(d)FROM test
---GROUP BY ROLLUP(a,b,c);
---等价于：
---SELECT a,b,c,SUM(d)FROM test GROUP BY a,b,c
--- UNION ALL
---SELECT a,b,null,SUM(d)FROM test GROUP BY a,b
--- UNION ALL
---SELECT a,null,null,SUM(d)FROM test GROUP BY a
--- UNION ALL
---SELECT null,null,null,SUM(d)FROM test
--- 1.3)对ROLLUP的列从右到左以一次少一列的方式进行分组直到所有列都去掉后的分组（也就是全表分组）
--- 1.4）对于n个参数的ROLLUP,有n+1次分组
--- 1.5）准备数据：2010-2011年每月每天的销售额

 

 

----随机插入一千条数据在里面：（随机数,随机生成。）
---ROLLUP(续3)
CREATE TABLE sales_tab_RR(
year_id NUMBER NOT NULL,
month_id NUMBER NOT NULL,
day_id NUMBER NOT NULL,
sales_value NUMBER(10,2)NOT NULL
);
INSERT INTO sales_tab_RR
SELECT TRUNC(DBMS_RANDOM.value(2010,2012))AS year_id,
TRUNC(DBMS_RANDOM.value(1,13))AS month_id,
TRUNC(DBMS_RANDOM.value(1,32))AS day_id,
ROUND(DBMS_RANDOM.value(1,100),2) AS sales_value
FROM dual
CONNECT BY level<=1000;
--->table SALES_TAB_RR 已创建。
--->1,000 行已插入。

SELECT year_id,month_id,day_id,sales_value
FROM sales_tab_RR;
---一个店铺、商店每天营业的流水情况。
SELECT year_id,month_id,day_id,sales_value
FROM sales_tab_RR
ORDER BY year_id,month_id,day_id;
---给它排序。

---查看每天营业额？
SELECT year_id,month_id,day_id, SUM(sales_value)
FROM sales_tab_RR
GROUP BY year_id,month_id,day_id
ORDER BY year_id,month_id,day_id;

---查看每月营业额？（把天去了，就是年、月相同的记录看成一组，对其求和）
SELECT year_id,month_id, SUM(sales_value)
FROM sales_tab_RR
GROUP BY year_id,month_id
ORDER BY year_id,month_id;

---查看每年营业额？
SELECT year_id, SUM(sales_value)
FROM sales_tab_RR
GROUP BY year_id;

---总共的营业额？
SELECT SUM(sales_value)
FROM sales_tab_RR;

--- 在一个结果集里这几种情况都出现：（每、年、月、日、总共都看）
SELECT year_id,month_id,day_id, SUM(sales_value)
FROM sales_tab_RR
GROUP BY year_id,month_id,day_id
UNION ALL
SELECT year_id,month_id,null,SUM(sales_value)
FROM sales_tab_RR
GROUP BY year_id,month_id
UNION ALL
SELECT year_id,null,null, SUM(sales_value)
FROM sales_tab_RR
GROUP BY year_id
UNION ALL
SELECT null,null,null,SUM(sales_value)
FROM sales_tab_RR;
--->太长了，恶心！

---ROLLUP()函数
---GROUP BY ROLLUP(a,b,c)
---等同于
---GROUP BY a,b,c
---UNION ALL
---GROUP BY a,b
---UNION ALL
---GROUP BY a
---UNION ALL
---全表

SELECT year_id,month_id,day_id, SUM(sales_value)
FROM sales_tab_RR
GROUP BY ROLLUP(year_id,month_id,day_id)
ORDER BY year_id,month_id,day_id;

SELECT year_id,month_id, SUM(sales_value)
FROM sales_tab_RR
GROUP BY ROLLUP(year_id,month_id)
ORDER BY year_id,month_id;
--->参数每次都是逐一递减，先按月，再按年，最后年月。

---CUBE()函数
---CUBE()函数会将所有参数的每种组合多作为一次分组方式进行统计然后将结果并在一起，
---所以分组次数为2的参数个数次方。

---CUBE立方体，所有维度都来一遍，所有组合都做一遍。
---GROUP BY CUBE(a,b,c)
---abc
---ab
---ac
---bc
---a
---b
---c
---全表
---加起来总共8种。

---四的立方就是16种。
SELECT year_id,month_id,day_id, SUM(sales_value)
FROM sales_tab_RR
GROUP BY CUBE(year_id,month_id,day_id)
ORDER BY year_id,month_id,day_id;

SELECT year_id,month_id, SUM(sales_value)
FROM sales_tab_RR
GROUP BY CUBE(year_id,month_id)
ORDER BY year_id,month_id;

---rowup还是cube都内定了分组的方式了。
---rowup字段逐一递减,cube所有组合都来一遍。
---rowup片面格式固定，cube太全面了。
---现在有一需求，想看每天和每月的，没意义的组合不想看。在一个结果集里。

---GROUPING SETS（比前两个要频繁，但这三个都不太用）
---允许自己去指定分组方式，然后帮你统计分组，再并到一个结果集里。

---GROUPING SETS()
---每一个参数是一种分组方式，然后将这些分组统计的结果并在一个结果集中显示。
---查看每天与每月营业额？
---SELECT year_id,month_id,day_id, SUM(sales_value)
---FROM sales_tab_RR
---GROUP BY GROUPING SETS(year_id,month_id,day_id)
---ORDER BY year_id,month_id,day_id;

---GROUPING SETS(year_id,month_id,day_id)
---等同于
---GROUP BY year_id
---UNION ALL
---GROUP BY month_id

SELECT year_id,month_id,day_id, SUM(sales_value)
FROM sales_tab_RR
GROUP BY GROUPING SETS((year_id,month_id,day_id),(year_id,month_id))
ORDER BY year_id,month_id,day_id;
--->放在一个括号里,表一个参数。